﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;

//using System.Data.OracleClient;
using System.Security.Cryptography;
using System.Text;
using System.Web;

namespace Utility
{
    public class OracleHelp
    {
        public OracleHelp()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        private string conn_str = ConfigurationManager.ConnectionStrings["sqlCon"].ConnectionString;

        #region 加密、解密函数定义处

        //默认密钥向量
        private byte[] Keys = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };

        //加密、解密字符串
        private string strCryptKey = "cqic0000";

        /// <summary>
        /// DES加密字符串
        /// </summary>
        /// <param name="encryptString">待加密的字符串</param>
        /// <returns>加密成功返回加密后的字符串，失败返回源串</returns>
        public string EncryptDES(string encryptString)
        {
            try
            {
                byte[] rgbKey = Encoding.UTF8.GetBytes(strCryptKey.Substring(0, 8));
                byte[] rgbIV = Keys;
                byte[] inputByteArray = Encoding.UTF8.GetBytes(encryptString);
                DESCryptoServiceProvider dCSP = new DESCryptoServiceProvider();
                MemoryStream mStream = new MemoryStream();
                CryptoStream cStream = new CryptoStream(mStream, dCSP.CreateEncryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
                cStream.Write(inputByteArray, 0, inputByteArray.Length);
                cStream.FlushFinalBlock();
                return Convert.ToBase64String(mStream.ToArray());
            }
            catch
            {
                return encryptString;
            }
        }

        /// <summary>
        /// DES解密字符串
        /// </summary>
        /// <param name="decryptString">待解密的字符串</param>
        /// <returns>解密成功返回解密后的字符串，失败返源串</returns>
        public string DecryptDES(string decryptString)
        {
            try
            {
                byte[] rgbKey = Encoding.UTF8.GetBytes(strCryptKey);
                byte[] rgbIV = Keys;
                byte[] inputByteArray = Convert.FromBase64String(decryptString);
                DESCryptoServiceProvider DCSP = new DESCryptoServiceProvider();
                MemoryStream mStream = new MemoryStream();
                CryptoStream cStream = new CryptoStream(mStream, DCSP.CreateDecryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
                cStream.Write(inputByteArray, 0, inputByteArray.Length);
                cStream.FlushFinalBlock();
                return Encoding.UTF8.GetString(mStream.ToArray());
            }
            catch
            {
                return decryptString;
            }
        }

        #endregion 加密、解密函数定义处

        private OracleConnection conn = null;

        /// <summary>
        /// 数据源
        /// </summary>
        /// <param name="conn_str">数据源连接字符串</param>
        public OracleConnection DataBase(string conn_str)
        {
            //string scon = conn_str.Split('$')[0];
            //string str = conn_str.Split('$')[1];

            conn = new OracleConnection(conn_str);
            //this.Open();
            return conn;
        }

        /// <summary>
        /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string sql_zcw)
        {
            DataSet ds_zcw = new DataSet();
            try
            {
                conn = DataBase(conn_str);
                this.Open();
                //OracleCommand cmd = new OracleCommand(sql, conn);
                //cmd.CommandTimeout = 20;
                OracleDataAdapter adapter_zcw = new OracleDataAdapter(sql_zcw, conn);

                adapter_zcw.Fill(ds_zcw, "tempTable");
            }
            catch
            {
                ds_zcw = null;
            }
            finally
            {
                this.Close();
                this.Dispose();
            }
            return ds_zcw;
        }

        /// <summary>
        /// 对数据库的增，删，改的操作带事务
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>是否成功</returns>
        public int ExecuteDataBase(string sql)
        {
            //bool succeed = false;
            int cnt = 0;
            OracleTransaction tx = null;
            try
            {
                conn = DataBase(conn_str);
                this.Open();
                tx = conn.BeginTransaction();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.Transaction = tx;
                //cmd.CommandTimeout = 20;
                cnt = cmd.ExecuteNonQuery();
                tx.Commit();
            }
            catch (Exception e)
            {
                tx.Rollback();
                throw e;
            }
            finally
            {
                this.Close();
                this.Dispose();
            }
            return cnt;
        }

        /// <summary>
        /// 增 删 改 复合语句 带事务
        /// </summary>
        /// <param name="sql">以;间隔的SQL语句</param>
        /// <returns>1 成功 0 失败</returns>
        public int ExecuteDataBaseEx(List<string> sqls)
        {
            int cnt = 0;
            OracleTransaction tx = null;

            try
            {
                conn = DataBase(conn_str);

                this.Open();

                tx = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.Transaction = tx;

                for (int i = 0; i < sqls.Count; i++)
                {
                    cmd.CommandText = sqls[i];
                    cnt += cmd.ExecuteNonQuery();
                }

                tx.Commit();

                cnt = 1;
            }
            catch (Exception ex)
            {
                tx.Rollback();

                cnt = 0;
                throw ex;
            }
            finally
            {
                this.Close();
                this.Dispose();
            }

            return cnt;
        }

        public int ExecuteDataBaseEx(string sql)
        {
            string[] sqls = sql.Split(';');

            int cnt = 0;
            OracleTransaction tx = null;

            try
            {
                conn = DataBase(conn_str);

                this.Open();

                tx = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.Transaction = tx;

                for (int i = 0; i < sqls.Length; i++)
                {
                    cmd.CommandText = sqls[i];
                    cnt += cmd.ExecuteNonQuery();
                }

                tx.Commit();

                cnt = 1;
            }
            catch (Exception ex)
            {
                tx.Rollback();

                cnt = 0;
                throw ex;
            }
            finally
            {
                this.Close();
                this.Dispose();
            }

            return cnt;
        }

        /// <summary>
        /// 防止SQL恶意注入式执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="indexPname"></param>
        /// <param name="vals"></param>
        /// <returns></returns>
        //public int ExecuteDataBase(string sql, string[] indexPname, string[] vals)
        //{
        //    //bool succeed = false;
        //    int cnt = 0;
        //    OracleTransaction tx = null;
        //    try
        //    {
        //        conn = DataBase(conn_str);
        //        this.Open();
        //        tx = conn.BeginTransaction();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.Connection = conn;
        //        cmd.CommandText = sql;

        //        for (int i = 0; i < indexPname.Length; i++)
        //        {
        //            if (indexPname[i].Split('@').Length == 2)
        //            {
        //                if (indexPname[i].Split('@')[1].Equals("BLOB"))
        //                {
        //                    cmd.Parameters.Add(indexPname[i].Split('@')[0], OracleType.Blob);

        //                    cmd.Parameters[indexPname[i].Split('@')[0]].Value = vals[i];
        //                }
        //            }
        //            else
        //            {
        //                cmd.Parameters.Add(indexPname[i], vals[i]);
        //            }
        //        }
        //        cmd.Transaction = tx;
        //        //cmd.CommandTimeout = 20;
        //        cnt = cmd.ExecuteNonQuery();
        //        tx.Commit();
        //    }
        //    catch (Exception e)
        //    {
        //        tx.Rollback();
        //        throw e;
        //    }
        //    finally
        //    {
        //        this.Close();
        //        this.Dispose();
        //    }
        //    return cnt;
        //}

        /// <summary>
        /// BLOB写入
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="data"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        //public void ExecuteDataBaseBLOB(string sql, byte[] data)
        //{
        //    OracleTransaction tx = null;
        //    try
        //    {
        //        conn = DataBase(conn_str);
        //        this.Open();
        //        tx = conn.BeginTransaction();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.Connection = conn;
        //        cmd.CommandText = sql;
        //        cmd.Transaction = tx;
        //        OracleDataReader reader = cmd.ExecuteReader();
        //        using (reader)
        //        {
        //            reader.Read();

        //            OracleLob tempLob = reader.GetOracleLob(0);

        //            // 将文件写入 BLOB 中
        //            tempLob.Write(data, 0, data.Length);

        //            tempLob.EndBatch();

        //            cmd.Parameters.Clear();
        //        }

        //        //cnt = cmd.ExecuteNonQuery();
        //        tx.Commit();
        //    }
        //    catch (Exception e)
        //    {
        //        tx.Rollback();
        //        throw e;
        //    }
        //    finally
        //    {
        //        this.Close();
        //        this.Dispose();
        //    }
        //}

        /// <summary>
        /// 存储过程增删改
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="sp">存储过程输入参数,无参数就输入null</param>
        /// <returns></returns>
        public int RunSQL(string procName, OracleParameter[] sp)
        {
            int count = 0;
            try
            {
                conn = DataBase(conn_str);
                this.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procName;
                cmd.Connection = conn;
                if (sp != null)
                {
                    foreach (OracleParameter para in sp)
                    {
                        cmd.Parameters.Add(para);
                    }
                }

                count = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
                this.Dispose();
            }
            return count;
        }

        /// <summary>
        ///  获得该SQL查询返回DataTable，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable tb = null;
            DataSet ds = this.GetDataSet(sql);
            if (ds != null)
            {
                tb = ds.Tables["tempTable"];
            }
            return tb;
        }

        /// <summary>
        /// 打开数据库连接.
        /// </summary>
        private bool Open()
        {
            bool succeed = false;
            try
            {
                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                    succeed = true;
                }
                else if (conn.State == System.Data.ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                    succeed = true;
                }
                else if (conn.State == System.Data.ConnectionState.Open)
                {
                    succeed = true;
                }
            }
            catch (Exception e)
            {
                e.ToString();
            }

            return succeed;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (conn != null)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 释放数据库连接资源
        /// </summary>
        public void Dispose()
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }

        /// <summary>
        /// 获取行数
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <returns></returns>
        public int getCount(string sql)
        {
            int count = 0;
            DataSet ds = new DataSet();
            try
            {
                // OracleClob
                conn = DataBase(conn_str);
                this.Open();

                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = sql;
                cmd.Connection = conn;
                cmd.CommandTimeout = 20;
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                adapter.Fill(ds, "tempTable");
                if (int.Parse(ds.Tables["tempTable"].Rows[0][0].ToString()) > 0)
                {
                    count = int.Parse(ds.Tables["tempTable"].Rows[0][0].ToString());
                }
            }
            catch (Exception e)
            {
                //此处写入错误日志
                StringBuilder xsb = new StringBuilder();
                xsb.Append("<?xml version=\"1.0\"?>\r\n");
                xsb.Append("<root>\r\n");
                xsb.Append("<ErrorInfo>" + e.Message + "</ErrorInfo>\r\n");

                xsb.Append("<ExcuteSQL>" + sql + "</ExcuteSQL>\r\n");
                xsb.Append("<DataFor>ExcuteQuery</DataFor>\r\n");
                xsb.Append("</root>\r\n");
                CreateErrorDOC(xsb.ToString());

                ds = null;
            }
            finally
            {
                this.Close();
                this.Dispose();
            }
            return count;
        }

        private static Object xmllogTran = new Object();

        public string CreateErrorDOC(string xmlSTR)
        {
            lock (xmllogTran)
            {
                string url = BuildPath("~/Error/");//Server.MapPath(@"~/Error/");
                string filename = "Error" + DateTime.Now.ToString("yyyyMMddss") + DateTime.Now.Millisecond.ToString() + ".xml";
                string fullpath = url + filename;
                File.AppendAllText(fullpath, xmlSTR);
            }

            return "1";
        }

        private static string BuildPath(string path)
        {
            //如果有path就转换为绝对路径
            if (!Path.IsPathRooted(path))
            {
                path = HttpContext.Current.Server.MapPath(path);
            }
            //如果没有`"~/Upload/PROOther"`这个文件夹就创建
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            return Path.GetFullPath(path);
        }

        /// <summary>
        /// 存储过程DATATABLE查询
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public DataTable ExcueteDataTable(string procName, OracleParameter[] sp)
        {
            DataSet ds = new DataSet();
            try
            {
                conn = DataBase(conn_str);
                this.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procName;
                cmd.Connection = conn;
                if (sp != null)
                {
                    foreach (OracleParameter para in sp)
                    {
                        cmd.Parameters.Add(para);
                    }
                }

                OracleDataAdapter sda = new OracleDataAdapter();
                sda.SelectCommand = cmd;
                sda.Fill(ds, "tempTable");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
                this.Dispose();
            }
            return ds.Tables["tempTable"];
        }

        /// <summary>
        /// 存储过程带返回值
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="sp">存储过程输入参数,无参数就输入null</param>
        /// <returns></returns>
        public string RunSQLEX(string procName, OracleParameter[] sp, int outArgIndex)
        {
            int count = 0;
            string result = "";
            try
            {
                conn = DataBase(conn_str);
                this.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procName;
                cmd.Connection = conn;
                if (sp != null)
                {
                    foreach (OracleParameter para in sp)
                    {
                        cmd.Parameters.Add(para);
                    }
                }
                count = cmd.ExecuteNonQuery();
                result = sp[outArgIndex].Value.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
                this.Dispose();
            }
            return result;
        }

        #region 新Db方法

        public IDbConnection GetConecttion()
        {
            var conn = new OracleConnection(conn_str);
            return conn;
        }

        public int NoQuery(string sql, List<Tuple<string, string, string>> parms)
        {
            using (var _conn = GetConecttion())
            {
                return NoQuery(_conn, sql, parms);
            }
        }

        public int NoQuery(IDbConnection _conn, string sql, List<Tuple<string, string, string>> parms)
        {
            return NoQuery(_conn as OracleConnection, sql, parms);
        }

        public int NoQuery(OracleConnection _conn, string sql, List<Tuple<string, string, string>> parms)
        {
            try
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.BindByName = true;
                    cmd.Connection = _conn;
                    cmd.CommandText = sql;
                    if (_conn.State != ConnectionState.Open)
                    {
                        _conn.Open();
                    }
                    if (parms != null)
                    {
                        cmd.Parameters.AddRange(ConvertToParms(parms).ToArray());
                    }
                    int result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return result;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Query(string sql, List<Tuple<string, string, string>> parms)
        {
            using (var _conn = GetConecttion())
            {
                return Query(_conn, sql, parms);
            }
        }

        public DataTable Query(IDbConnection _conn, string sql, List<Tuple<string, string, string>> parms)
        {
            return Query(_conn as OracleConnection, sql, parms);
        }

        public DataTable Query(OracleConnection _conn, string sql, List<Tuple<string, string, string>> parms)
        {
            try
            {
                using (var cmd = new OracleCommand())
                {
                    cmd.BindByName = true;
                    cmd.Connection = _conn;
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    if (_conn.State != ConnectionState.Open)
                    {
                        _conn.Open();
                    }
                    if (parms != null)
                    {
                        cmd.Parameters.AddRange(ConvertToParms(parms).ToArray());
                    }
                    using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
                    {
                        var dt = new DataTable("Table");
                        adapter.Fill(dt);
                        cmd.Parameters.Clear();
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private List<OracleParameter> ConvertToParms(List<Tuple<string, string, string>> parms)
        {
            var arry = new List<OracleParameter>();
            foreach (var item in parms)
            {
                var parm = new OracleParameter();
                parm.ParameterName = item.Item1;

                #region 设置类型

                switch (item.Item3)
                {
                    case "int":
                        {
                            parm.OracleDbType = OracleDbType.Int32;
                            break;
                        }
                    case "string":
                        {
                            parm.OracleDbType = OracleDbType.Varchar2;
                            break;
                        }
                    case "datetime":
                        {
                            parm.OracleDbType = OracleDbType.Date;
                            break;
                        }
                    case "decimal":
                        {
                            parm.OracleDbType = OracleDbType.Decimal;
                            break;
                        }
                    default:
                        {
                            parm.OracleDbType = OracleDbType.Varchar2;
                            break;
                        }
                }

                #endregion 设置类型

                #region 设置值

                if (item.Item2 == null)
                {
                    parm.IsNullable = true;
                    parm.Value = DBNull.Value;
                }
                else
                {
                    switch (item.Item3)
                    {
                        case "int":
                            {
                                parm.Value = Convert.ToInt32(item.Item2);
                                break;
                            }
                        case "string":
                            {
                                parm.Value = Convert.ToString(item.Item2);
                                break;
                            }
                        case "datetime":
                            {
                                parm.Value = Convert.ToDateTime(item.Item2);
                                break;
                            }
                        case "decimal":
                            {
                                parm.Value = Convert.ToDecimal(item.Item2);
                                break;
                            }
                        default:
                            {
                                parm.Value = Convert.ToString(item.Item2);
                                break;
                            }
                    }
                }

                #endregion 设置值

                arry.Add(parm);
            }
            return arry;
        }

        #endregion 新Db方法
    }
}